/**
 * @Copyright: Interactive Business Solution Pte Ltd, Singapore
 * @Author: Li Wen, 2007-12-18
 * @Version: 071231.003
 * Data access object of price model
 */
package com.ibsol.database
{
	import com.ibsol.model.Price;
	
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	
	public class PriceDAO
	{
		private var _tourixDBConn:SQLConnection;
		/**
		 * Default constructor of Price DAO, get database connection
		 */
		public function PriceDAO()
		{
			_tourixDBConn = DBConnManager.getConnection("tourix");
		}
		/**
		 * Load price according to package id
		 * @param:	packageid:int			package's id, price belongs to
		 * 			resultHandler:Function	result function adapter, return price item array collection
		 * 			faultHandler:Function	fault funcition adapter
		 */
		public function loadPriceByPackage(packageid:int, resultHandler:Function, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "SELECT * FROM price WHERE packageid = " + packageid + " AND offlineOperator != 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					resultHandler.call(this, typeArray(sqlStr.getResult().data));
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Insert a new price record into database.
		 * Price (id, packageid, type, P1A, P2A, P3A, P1A1C, P2A1C, P2A2C, tax, offlineOperator)
		 * @param:		price:Price					price object that will be added
		 * 				resultHandler:Function		result handler function adapter
		 * 				faultHandler:Function		fault handler function adapter
		 * @return:		void
		 */
		public function insertPrice(price:Price, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "INSERT INTO price (packageid, type, P1A, P2A, P3A, P1A1C, P2A1C, P2A2C, tax, offlineOperator, updateTime) " + 
					"values (" + price.packageid + ",'" + price.type + "'," + price.p1a + "," + price.p2a + "," + price.p3a + "," + 
					price.p1a1c + "," + price.p2a1c + "," + price.p2a2c + "," + price.tax + ",'NEW', 'T')";
			sqlStr.addEventListener(SQLEvent.RESULT, function (event:SQLEvent):void
			{
				price.id = sqlStr.getResult().lastInsertRowID;
				if(resultHandler != null)
					resultHandler.call(this, price.id);
			});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Updates a price record in database. For different level of user, there are different options.
		 * price(id, packageid, type, P1A, P2A, P3A, P1A1C, P2A1C, P2A2C, tax, offlineOperator)
		 * @param:	price:Price				price object which will be updated
		 * 			resultHandler:Function	result handler function
		 * 			faultHandler:Function	fault handler function
		 * @return: void
		 */	
		public function updatePrice(price:Price, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE  price SET packageid=" + price.packageid + ",type='" + price.type + "',P1A=" + price.p1a + ",P2A=" + price.p2a + 
					",P3A=" + price.p3a + ",P1A1C=" + price.p1a1c + ",P2A1C=" + price.p2a1c + ",P2A2C=" + price.p2a2c + ",tax=" + price.tax + 
					",offlineOperator = 'EDIT', updateTime = 'T' WHERE id = " + price.id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Deletes prices offline, just sets the price table field offlineOperator to "DEL".
		 * @param:	id:int					package id, whose prices need to be deleted
		 * 			resultHandler:Function	result handler adapter
		 * 			faultHandler:Function	fault handler adapter
		 * @return:	void
		 */
		public function deletePrices(id:int, resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE price SET offlineOperator='DEL', updateTime = 'T' WHERE packageid = " + id;
			sqlStr.addEventListener(SQLEvent.RESULT,
				function(event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Permentately delete price from database, whose offlineOperator is set to "DEL". If user wants to delete price, 
		 * user just updates offlineOperator to "DEL".
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return:	void
		 */
		public function clearDeletedPrices(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "DELETE * FROM price WHERE offlineOperator = 'DEL'";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Sets offline operator to "", this function called after synchronization with server
		 * @param:	resultHandler:Function		result handler adapter
		 * 			faultHandler:Function		fault handler adapter
		 * @return: void
		 */
		public function resetOfflineOperator(resultHandler:Function = null, faultHandler:Function = null):void
		{
			var sqlStr:SQLStatement = new SQLStatement();
			sqlStr.sqlConnection = _tourixDBConn;
			sqlStr.text = "UPDATE price SET offlineOperator=''";
			sqlStr.addEventListener(SQLEvent.RESULT,
				function (event:SQLEvent):void
				{
					if(resultHandler != null)
						resultHandler.call(this);
				});
			sqlStr.addEventListener(SQLErrorEvent.ERROR, faultHandler == null ? sqlErrorHandler : faultHandler);
			sqlStr.execute();
		}
		/**
		 * Converts database object to Price class
		 * @param:	o:Object	database return object
		 * @return:	Price		Price object after conversion
		 */
		private function typeObject(o:Object):Price
		{
			var p:Price = new Price();
			p.id = o.id;
			p.packageid = o.packageid;
			p.type = o.type;
			p.p1a = o.p1a;
			p.p2a = o.p2a;
			p.p3a = o.p3a;
			p.p1a1c = o.p1a1c;
			p.p2a1c = o.p2a1c;
			p.p2a2c = o.p2a2c;
			p.tax = o.tax;
			p.offlineOperator = o.offlineOperator;
			return p;
		}
		/**
		 * Converts array object to Photo array collection
		 * @param: a:Array array of objects returned by SQL
		 * @return: ArrayCollection of Photo after conversion from Array
		 */
		private function typeArray(a:Array):ArrayCollection
		{
			if (a==null) return null;
			var ac:ArrayCollection = new ArrayCollection();
			for (var i:int; i<a.length; i++)
			{
				ac.addItem(typeObject(a[i]));
			}
			return ac;
		}
		/**
		 * Default SQL error handler, if above functions' fault handlers are null, this error handler is applied
		 * @param: event:SQLError	SQL Error event will be handled if there is an error
		 * @return: void
		 */
		private function sqlErrorHandler(event:SQLErrorEvent):void
		{
			mx.controls.Alert.show(event.text, "Error");
			trace(event.text);
		}
	}
}